package fy.form.listen.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONObject;

import fy.form.listen.bean.LogBean;
import fy.util.DBUtil;

public class LogDao {

	private List<Long> summaryIdList;

	private String driverClassName;
	private String url;
	private String userName;
	private String password;

	public LogDao(String driverClassName, String url, String userName,
			String password) {
		this.driverClassName = driverClassName;
		this.url = url;
		this.userName = userName;
		this.password = password;
	}

	public List<JSONObject> findLogInfo(List<Long> summaryIdList) {
		this.summaryIdList = summaryIdList;

		List<JSONObject> jsonList = new ArrayList<JSONObject>();
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.connect(driverClassName, url, userName, password);

			st = conn.prepareStatement(getSQL());
			setParams(st);
			
			rs = st.executeQuery();

			while (rs.next()) {
				// c.subject, m.name, d.name, a.name,
				// to_char(c.start_date, 'yyyy-mm-dd hh24:mm:ss'),
				// to_char(c.finish_date, 'yyyy-mm-dd hh24:mm:ss'), f.appname

				LogBean lb = new LogBean();

				lb.setTitle(rs.getString(1));
				lb.setSender(rs.getString(2));
				lb.setDep(rs.getString(3));
				lb.setAccount(rs.getString(4));
				lb.setStartTime(rs.getString(5));
				lb.setFinishTime(rs.getString(6));
				lb.setFormName(rs.getString(7));

				jsonList.add(lb.getJSON());

				lb = null;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(st);
			DBUtil.setAutoCommit(conn, true);
			DBUtil.close(conn);
		}
		return jsonList;
	}

	private String getSQL() {
		StringBuilder sb = new StringBuilder();
		sb.append("select c.subject, m.name, d.name, a.name,to_char(c.start_date, 'yyyy-mm-dd hh24:mi:ss'), to_char(c.finish_date, 'yyyy-mm-dd hh24:mi:ss'), f.appname ");
		sb.append("from col_summary c , v3x_org_member m , v3x_org_department d, ");
		sb.append("v3x_org_account a, form_appmain f ");
		sb.append("where c.start_member_id = m.id and m.org_department_id = d.id ");
		sb.append("and m.org_account_id = a.id and c.form_appid = f.id and ( ");

		for (int i = 0, size = summaryIdList.size(); i < size; i++) {
			if (i > 0) {
				sb.append(" or ");
			}
			sb.append(" c.id=? ");
		}
		sb.append(")");

		sb.append(" order by c.finish_date desc");

		return sb.toString();
	}

	private void setParams(PreparedStatement st) throws Exception {
		for (int i = 0, size = summaryIdList.size(); i < size; i++) {
			st.setString(i + 1, String.valueOf(summaryIdList.get(i)));
		}
	}
}
